
from publicHandle.publicHandle import conn, cur, returnData

# 学生选课
def studentSelect(courseId, studentIds):
    connection =conn()
    cursor = cur(connection)
    mysql_deleteAllStudent = "delete from sc where courseNumber = %s"
    mysql_selectCourse = "insert into sc(courseNumber,studentNumber) values (%s,%s)"
    print(studentIds)
    try:
        cursor.execute(mysql_deleteAllStudent,(courseId))
        for i in studentIds:
            cursor.execute(mysql_selectCourse, (courseId, i))
        return returnData({"success": "true"})
    except:
        connection.rollback()
        return returnData({}, "访问数据库失败", 401)
    finally:
        connection.commit()
        connection.close()
        cursor.close()

# 课程打分
def editCourseScore(courseId, studentId, score):
    connection = conn()
    cursor = cur(connection)
    mysql_updateScore = "update sc set score = %s where courseNumber = %s and studentNumber = %s"
    try:
        cursor.execute(mysql_updateScore, (score, courseId, studentId))
        return returnData({"success": "true"})
    except:
        return returnData({"success": "false"}, "访问数据库失败",401)
    finally:
        connection.commit()
        connection.close()
        cursor.close()


# 查询课程分数
def selectCourseScore(courseId):
    connection = conn()
    cursor = cur(connection)
    mysql_selectCourseScore = "select student.name, sc.score,student.number from sc,student where sc.courseNumber = %s and sc.studentNumber = student.number"
    try:
        cursor.execute(mysql_selectCourseScore, (courseId))
        data = cursor.fetchall()
        temp = {}
        arr = []
        print(data)
        if data != None:
            for i in data:
                temp["name"] = i[0]
                temp["score"] = i[1]
                temp["studentId"] = i[2]
                arr.append(temp.copy())
            return returnData(arr)
        else:
            return returnData({"success": "true"}, "该课程没有学生")
    except:
        return returnData({"success": "false"}, "访问数据库失败", 401)
    finally:
        connection.commit()
        connection.close()
        cursor.close()
